/*
 * MIT License
 *
 * Copyright (c) 2024-2048 冰羽
 *
 * Permission is hereby granted, free of charge, to any person obtaining a copy
 * of this software and associated documentation files (the "Software"), to deal
 * in the Software without restriction, including without limitation the rights
 * to use, copy, modify, merge, publish, distribute, sublicense, and/or sell
 * copies of the Software, and to permit persons to whom the Software is
 * furnished to do so, subject to the following conditions:
 *
 * The above copyright notice and this permission notice shall be included in all
 * copies or substantial portions of the Software.
 *
 * THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
 * IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY,
 * FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE
 * AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER
 * LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM,
 * OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE
 * SOFTWARE.
 */

package cn.star.framework.extend.service;

import cn.hutool.core.collection.CollectionUtil;
import cn.hutool.core.lang.Assert;
import cn.hutool.core.lang.Validator;
import cn.hutool.core.util.IdUtil;
import cn.hutool.core.util.ReflectUtil;
import cn.hutool.core.util.StrUtil;
import cn.star.framework.core.api.Pageable;
import cn.star.framework.extend.core.Column;
import cn.star.framework.extend.core.Database;
import cn.star.framework.extend.core.PrimaryKey;
import cn.star.framework.extend.core.Table;
import cn.star.framework.extend.entity.EntityWrapper;
import cn.star.framework.extend.type.TableType;
import cn.star.framework.sql.Identifier;
import cn.star.framework.sql.standard.Sorter;
import cn.star.framework.sql.standard.Where;
import cn.star.framework.sql.standard.WhereGroup;
import cn.star.framework.support.convert.FieldConverterCache;
import cn.star.framework.type.BooleanOperator;
import cn.star.framework.type.QueryType;
import com.fasterxml.jackson.annotation.JsonProperty;
import com.fasterxml.jackson.annotation.JsonProperty.Access;
import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.math.BigInteger;
import java.util.ArrayList;
import java.util.Collections;
import java.util.HashMap;
import java.util.List;
import java.util.Map;
import java.util.stream.Collectors;
import javax.persistence.AttributeConverter;
import javax.persistence.Tuple;
import javax.persistence.TupleElement;
import lombok.Getter;
import lombok.Setter;
import lombok.extern.slf4j.Slf4j;
import org.hibernate.Session;
import org.hibernate.dialect.Dialect;
import org.hibernate.query.NativeQuery;

/**
 * AbstractService
 *
 * @author zhaoweiping
 *     <p style='color: red'>Created on 2024-09-23 11:06:00
 * @since 3.0.0
 */
@Slf4j
public abstract class AbstractService implements IService {
  /**
   * 获取实体字段列表
   *
   * @param type 实体类型
   * @return 实体字段列表
   */
  private List<Field> getEntityFields(Class<?> type) {
    List<Field> fields = new ArrayList<>();
    if (type != null) {
      fields.addAll(CollectionUtil.toList(ReflectUtil.getFields(type)));
    }
    return fields;
  }

  /**
   * 获取数据表列名对应的Java实体字段
   *
   * @param fields 实体字段列表
   * @param columnName 数据表列名
   * @return 实体字段
   */
  private Field getEntityFieldMappingColumnName(List<Field> fields, String columnName) {
    for (Field field : fields) {
      String name = StrUtil.toUnderlineCase(field.getName());
      javax.persistence.Column column = field.getAnnotation(javax.persistence.Column.class);
      if (column != null && StrUtil.isNotBlank(column.name())) {
        name = column.name();
      }
      // 精准匹配/字段名不区分大小写
      if (columnName.equalsIgnoreCase(name)) {
        return field;
      }
    }

    return null;
  }

  /**
   * 获取列值转换函数
   *
   * @param type 实体类型
   * @param columnNames 数据库表列名
   */
  private Map<String, AttributeConverter<Object, Object>> getColumnConverterMap(
      Class<?> type, List<String> columnNames) {
    Map<String, AttributeConverter<Object, Object>> map = new HashMap<>();
    List<Field> fields = this.getEntityFields(type);
    for (String columnName : columnNames) {
      Field field = this.getEntityFieldMappingColumnName(fields, columnName);
      AttributeConverter<Object, Object> converter =
          FieldConverterCache.getConverterInstance(field);
      if (converter != null) {
        map.put(columnName, converter);
      }
    }

    return map;
  }

  /**
   * 将实体数据转换为表数据
   *
   * @param type 实体类型
   * @param columnNames 表列
   * @param entity 实体数据
   * @return 表数据
   */
  private Map<String, Object> entity2map(
      Class<?> type,
      List<String> columnNames,
      Object entity,
      Map<String, AttributeConverter<Object, Object>> columnConverterMap) {
    Map<String, Object> result = new HashMap<>();

    List<Field> fields = this.getEntityFields(type);
    for (String columnName : columnNames) {
      Field field = this.getEntityFieldMappingColumnName(fields, columnName);
      if (field != null) {
        Object value = ReflectUtil.getFieldValue(entity, field);
        AttributeConverter<Object, Object> converter = columnConverterMap.get(columnName);
        result.put(
            columnName, converter == null ? value : converter.convertToDatabaseColumn(value));
      } else {
        log.warn(
            "database table column name [{}] not mapping [{}] field", columnName, type.getName());
      }
    }

    return result;
  }

  /**
   * 使用数据库表列名获取数据
   *
   * @param map 原始数据{@link EntityWrapper#getData()}
   * @param columnName 数据库表列名
   */
  private Object getMapValue(Object map, String columnName) {
    Object value;
    String name = StrUtil.toCamelCase(columnName);

    if (map instanceof Map) {
      Map m = (Map) map;
      value = m.get(columnName);
      if (value == null) {
        String key =
            Validator.isUpperCase(columnName.replaceAll("_", ""))
                ? columnName.toLowerCase()
                : columnName.toUpperCase();
        value = m.get(key);
      }
      value = value == null ? m.get(name) : value;
    } else {
      value = ReflectUtil.getFieldValue(map, columnName);
      value = value == null ? ReflectUtil.getFieldValue(map, name) : value;
    }

    return value;
  }

  /**
   * 原始数据转换为表数据
   *
   * @param columnNames 表列
   * @param map 原始数据
   * @return 表数据
   */
  private Map<String, Object> map2map(
      List<String> columnNames,
      Object map,
      Map<String, AttributeConverter<Object, Object>> columnConverterMap) {
    Map<String, Object> result = new HashMap<>();
    for (String columnName : columnNames) {
      AttributeConverter<Object, Object> converter = columnConverterMap.get(columnName);
      Object value = this.getMapValue(map, columnName);
      result.put(columnName, converter == null ? value : converter.convertToDatabaseColumn(value));
    }

    return result;
  }

  /**
   * 原始数据转换为表数据
   *
   * @param type 实体类型
   * @param columnNames 表列
   * @param value 原始数据
   * @return 表数据
   */
  private Map<String, Object> convert(
      Class<?> type,
      List<String> columnNames,
      Object value,
      Map<String, AttributeConverter<Object, Object>> columnConverterMap) {
    if (type == null || value instanceof Map) {
      // 不绑定实体类型，当实体类型为空时，直接根据Map转换为Map，和实体类型无关
      return this.map2map(columnNames, value, columnConverterMap);
    } else {
      return this.entity2map(type, columnNames, value, columnConverterMap);
    }
  }

  /**
   * 原始数据转换为表数据
   *
   * @param type 实体类型
   * @param table {@link Table}
   * @param data 原始数据
   */
  private List<Map<String, Object>> getTableValues(Class<?> type, Table table, List<?> data) {
    List<Map<String, Object>> result = new ArrayList<>();

    if (CollectionUtil.isNotEmpty(data)) {
      Map<String, AttributeConverter<Object, Object>> columnConverterMap =
          this.getColumnConverterMap(type, table.getColumnNames());
      for (Object value : data) {
        if (value == null) {
          continue;
        }
        Map<String, Object> item =
            this.convert(type, table.getColumnNames(), value, columnConverterMap);
        result.add(item);
      }
    }

    return result;
  }

  /**
   * 判断数据是否为空
   *
   * @param data 原始数据
   */
  private void checkDataEmpty(List<?> data) {
    Assert.isFalse(data == null || data.isEmpty(), "data is empty");
  }

  @Override
  public void save(EntityWrapper wrapper) {
    this.checkDataEmpty(wrapper.getData());
    Table table = wrapper.getDatabase().getTableInfo(wrapper.getTableName());
    Session session = wrapper.getSession();
    List<Map<String, Object>> list =
        this.getTableValues(wrapper.getType(), table, wrapper.getData());
    List<String> columnNames = table.getColumnNames();
    List<String> quoteColumnNames = this.wrapperQuoteColumnNames(wrapper, columnNames);
    for (Map<String, Object> value : list) {
      String sql =
          StrUtil.format(
              "insert into {} ({}) values ({})",
              table.getName(),
              String.join(", ", quoteColumnNames),
              columnNames.stream()
                  .map(columnName -> StrUtil.format(":{}", columnName))
                  .collect(Collectors.joining(", ")));

      NativeQuery<?> nativeQuery = session.createNativeQuery(sql);
      columnNames.forEach(
          columnName -> nativeQuery.setParameter(columnName, value.get(columnName)));
      nativeQuery.executeUpdate();
    }
  }

  /**
   * 删除SQL
   *
   * @param table {@link Table}
   * @param whereGroupTuple {@link WhereGroupTuple}
   */
  private void delete(EntityWrapper wrapper, Table table, WhereGroupTuple whereGroupTuple) {
    Session session = wrapper.getSession();
    List<WhereGroup> whereGroups = whereGroupTuple.getWhereGroups();
    String sql =
        StrUtil.format(
            "delete from {} where 1=1 {}",
            table.getName(),
            getWhereGroupClause(wrapper, whereGroups));

    NativeQuery<?> nativeQuery = session.createNativeQuery(sql);

    setNativeQueryWhereParameters(nativeQuery, whereGroups);

    nativeQuery.executeUpdate();
  }

  @Override
  public void delete(EntityWrapper wrapper) {
    Table table = wrapper.getDatabase().getTableInfo(wrapper.getTableName());
    WhereGroupTuple whereGroupTuple = this.getWhereGroupTuple(wrapper, table);
    this.checkWhereEmpty("delete", whereGroupTuple);

    List<Map<String, Object>> list =
        this.getTableValues(wrapper.getType(), table, wrapper.getData());
    Assert.isFalse(
        whereGroupTuple.getType() == WhereGroupType.PRIMARY_KEY && CollectionUtil.isEmpty(list),
        "delete where is empty, type [{}] data is empty",
        whereGroupTuple.getType());

    // 以Where条件优先
    if (whereGroupTuple.getType() == WhereGroupType.PRIMARY_KEY) {
      for (Map<String, Object> value : list) {
        setWherePrimaryKeyColumnsValue(whereGroupTuple, value);
        delete(wrapper, table, whereGroupTuple);
      }
    } else {
      delete(wrapper, table, whereGroupTuple);
    }
  }

  /** 条件类型 */
  private enum WhereGroupType {
    /** 主键条件、需要在使用过程中填充主键值 */
    PRIMARY_KEY,
    /** 普通条件 */
    NORMAL,
  }

  /** 条件元组 */
  private static class WhereGroupTuple {

    /** 类型 */
    @Getter @Setter private WhereGroupType type;

    /** 条件 */
    @Getter @Setter private List<WhereGroup> whereGroups;

    /** 主键列名 */
    @Getter @Setter private List<String> primaryKeyColumnNames;

    public WhereGroupTuple(WhereGroupType type, List<WhereGroup> whereGroups) {
      this.type = type;
      this.whereGroups = whereGroups;
    }

    public WhereGroupTuple(
        WhereGroupType type, List<WhereGroup> whereGroups, List<String> primaryKeyColumnNames) {
      this.type = type;
      this.whereGroups = whereGroups;
      this.primaryKeyColumnNames = primaryKeyColumnNames;
    }
  }

  /**
   * 检查传参过来的Where条件并构建标准的{@link WhereGroup}
   *
   * @param fields 实体字段类型
   * @param whereGroup {@link WhereGroup}
   * @param columnNames 数据库表列名
   * @return {@link WhereGroup}
   */
  private WhereGroup checkBuildWhereGroup(
      List<Field> fields, WhereGroup whereGroup, List<String> columnNames) {
    // 不用和实体字段进行比对、直接按照数据库表列判断
    if (whereGroup != null && CollectionUtil.isNotEmpty(whereGroup.getWheres())) {
      List<Where> wheres = whereGroup.getWheres(), list = new ArrayList<>();
      if (CollectionUtil.isNotEmpty(wheres)) {
        for (Where where : wheres) {
          Identifier identifier = where.getName();
          String name = identifier.getName();
          String columnName = identifier.getColumnName();
          String str =
              columnNames.stream()
                  .filter(i -> i.equalsIgnoreCase(name) || i.equalsIgnoreCase(columnName))
                  .findFirst()
                  .orElse(null);
          if (str != null) {
            Field field = this.getEntityFieldMappingColumnName(fields, columnName);
            Where w =
                new Where(
                    field == null ? new Identifier(str) : new Identifier(field),
                    where.getType(),
                    where.getValue());

            list.add(w);
          } else {
            log.warn("where check error, [{}] is no longer in columnNames", where.getColumnName());
          }
        }
      }

      whereGroup.setWheres(list);
    }

    return whereGroup;
  }

  /**
   * 获取Where条件
   *
   * @param ew {@link EntityWrapper}
   * @param table {@link Table}
   * @return 条件元组
   */
  private WhereGroupTuple getWhereGroupTuple(EntityWrapper ew, Table table) {
    List<Field> fields = this.getEntityFields(ew.getType());
    // Where优先
    // 以EntityWrapper#wheres(whereGroups)为准
    // 若存在wheres,则不会去取whereGroups；否则使用whereGroups
    List<Where> wheres = ew.getWheres();

    List<WhereGroup> list =
        CollectionUtil.isNotEmpty(wheres)
            ? Collections.singletonList(new WhereGroup(wheres))
            : ew.getWhereGroups();

    // 需要条件合法，即使用真正的表列名进行判断
    List<String> columnNames = table.getColumnNames();
    List<WhereGroup> whereGroups =
        list.stream()
            .map(whereGroup -> this.checkBuildWhereGroup(fields, whereGroup, columnNames))
            .filter(
                whereGroup ->
                    whereGroup != null && CollectionUtil.isNotEmpty(whereGroup.getWheres()))
            .collect(Collectors.toList());

    if (CollectionUtil.isEmpty(whereGroups)) {
      List<String> primaryKeyColumnNames = this.getPrimaryKeyColumnNames(table);
      if (CollectionUtil.isNotEmpty(primaryKeyColumnNames)) {
        whereGroups.add(
            new WhereGroup(
                primaryKeyColumnNames.stream()
                    .map(
                        columnName -> {
                          Field field = this.getEntityFieldMappingColumnName(fields, columnName);
                          return new Where(
                              field == null ? new Identifier(columnName) : new Identifier(field),
                              QueryType.EQ,
                              null);
                        })
                    .collect(Collectors.toList())));
      }

      return this.wrapper(
          new WhereGroupTuple(WhereGroupType.PRIMARY_KEY, whereGroups, primaryKeyColumnNames),
          table);
    } else {
      return this.wrapper(new WhereGroupTuple(WhereGroupType.NORMAL, whereGroups), table);
    }
  }

  private WhereGroupTuple wrapper(WhereGroupTuple whereGroupTuple, Table table) {
    List<Column> columns = table.getColumns();
    List<WhereGroup> whereGroups = whereGroupTuple.getWhereGroups();
    if (CollectionUtil.isNotEmpty(columns) && CollectionUtil.isNotEmpty(whereGroups)) {
      for (WhereGroup whereGroup : whereGroups) {
        List<Where> wheres = whereGroup.getWheres();
        if (CollectionUtil.isNotEmpty(wheres)) {
          for (Where where : wheres) {
            Identifier identifier = where.getName();
            if (identifier.getField() == null && identifier.getJdbcType() == null) {
              String columnName = identifier.getColumnName();
              Column column =
                  columns.stream()
                      .filter(i -> i.getName().equalsIgnoreCase(columnName))
                      .findFirst()
                      .orElse(null);
              if (column != null && column.getJdbcType() != null) {
                identifier.setJdbcType(column.getJdbcType());
              }
            }
          }
        }
      }
    }

    return whereGroupTuple;
  }

  /**
   * 获取数据库表主键列名集合
   *
   * @param table {@link Table}
   * @return 主键列名集合
   */
  private List<String> getPrimaryKeyColumnNames(Table table) {
    List<String> list = new ArrayList<>();

    PrimaryKey primaryKey = table.getPrimaryKey();
    if (primaryKey == null || CollectionUtil.isEmpty(primaryKey.getColumns())) {
      log.warn("table [{}] no primary keys", table.getName());
    } else {
      list = primaryKey.getColumns().stream().map(Column::getName).collect(Collectors.toList());
    }

    return list;
  }

  /**
   * 检查Where条件是否为空
   *
   * @param whereGroupTuple {@link WhereGroupTuple}
   * @param operator 操作类型
   */
  private void checkWhereEmpty(String operator, WhereGroupTuple whereGroupTuple) {
    Assert.isFalse(
        CollectionUtil.isEmpty(whereGroupTuple.getWhereGroups()), "{} where is empty", operator);
  }

  /** 生成唯一字符串 */
  private String getUniqueStrValue() {
    return IdUtil.simpleUUID();
  }

  /**
   * 获取一个Where条件参数名称
   *
   * @param columnName 数据库表列
   * @return Where条件参数名称
   */
  private String getWhereParameterName(String columnName) {
    return columnName + "_" + this.getUniqueStrValue();
  }

  private String getWhereClause(EntityWrapper wrapper, Where where) {
    String template = "";
    QueryType type = where.getType();
    if (type == QueryType.EQ) {
      template = "= :{}";
    } else if (type == QueryType.NE) {
      template = "!= :{}";
    } else if (type == QueryType.GT) {
      template = "> :{}";
    } else if (type == QueryType.GE) {
      template = ">= :{}";
    } else if (type == QueryType.LT) {
      template = "< :{}";
    } else if (type == QueryType.LE) {
      template = "<= :{}";
    } else if (type == QueryType.BETWEEN) {
      template = "between :{} and :{}";
    } else if (type == QueryType.NOT_BETWEEN) {
      template = "not between :{} and :{}";
    } else if (type == QueryType.LIKE
        || type == QueryType.LIKE_LEFT
        || type == QueryType.LIKE_RIGHT) {
      template = "like :{}";
    } else if (type == QueryType.NOT_LIKE) {
      template = "not like :{}";
    } else if (type == QueryType.NULL) {
      template = "is null";
    } else if (type == QueryType.NOT_NULL) {
      template = "is not null";
    } else if (type == QueryType.IN) {
      template = "in :{}";
    } else if (type == QueryType.NOT_IN) {
      template = "not in :{}";
    } else {
      throw new RuntimeException("Not support type: " + type);
    }

    List<String> parameterNames =
        new ArrayList<>(
            CollectionUtil.toList(
                this.getWhereParameterName(where.getColumnName()),
                this.getWhereParameterName(where.getColumnName())));

    String clause =
        StrUtil.format(
            "{} {}", this.wrapperQuoteColumnName(wrapper, where.getColumnName()), template);

    for (String parameterName : parameterNames) {
      clause = StrUtil.format(clause, parameterName);
    }

    where.setParameterNames(parameterNames);

    return clause;
  }

  /**
   * 设置主键列表Where条件列值
   *
   * @param whereGroupTuple {@link WhereGroupTuple}
   * @param value 表数据
   */
  private void setWherePrimaryKeyColumnsValue(
      WhereGroupTuple whereGroupTuple, Map<String, Object> value) {
    List<WhereGroup> whereGroups = whereGroupTuple.getWhereGroups();
    if (whereGroupTuple.getType() == WhereGroupType.PRIMARY_KEY) {
      for (WhereGroup whereGroup : whereGroups) {
        List<Where> wheres = whereGroup.getWheres();
        wheres.forEach(where -> where.setValue(value.get(where.getColumnName())));
      }
    }
  }

  /**
   * 条件SQL
   *
   * @param wrapper {@link EntityWrapper}
   * @param whereGroups {@link WhereGroup}
   */
  private String getWhereGroupClause(EntityWrapper wrapper, List<WhereGroup> whereGroups) {
    StringBuilder sb = new StringBuilder();
    for (int i = 0; i < whereGroups.size(); i++) {
      WhereGroup whereGroup = whereGroups.get(i);
      List<Where> wheres = whereGroup.getWheres();
      sb.append(whereGroup.getOperator() == BooleanOperator.AND ? "and " : "or ");

      sb.append("( ");
      for (int k = 0; k < wheres.size(); k++) {
        String clause = this.getWhereClause(wrapper, wheres.get(k));
        sb.append(clause).append(k == wheres.size() - 1 ? "" : " and ");
      }
      sb.append(" )").append(i == whereGroups.size() - 1 ? "" : " ");
    }

    return sb.toString();
  }

  /**
   * 设置查询参数
   *
   * @param nativeQuery {@link NativeQuery}
   * @param whereGroups {@link WhereGroup}
   */
  private void setNativeQueryWhereParameters(
      NativeQuery<?> nativeQuery, List<WhereGroup> whereGroups) {
    for (WhereGroup whereGroup : whereGroups) {
      List<Where> wheres = whereGroup.getWheres();
      for (Where where : wheres) {
        QueryType type = where.getType();
        AttributeConverter<Object, Object> converter =
            FieldConverterCache.getConverterInstance(where.getName().getField());
        Object value =
            where.getColumnValue(converter == null ? null : converter::convertToDatabaseColumn);
        if (type == QueryType.NULL || type == QueryType.NOT_NULL) {
          continue;
        }
        if (type == QueryType.BETWEEN || type == QueryType.NOT_BETWEEN) {
          List<?> values = (List<?>) value;
          nativeQuery.setParameter(where.getParameterNames().get(0), values.get(0));
          nativeQuery.setParameter(where.getParameterNames().get(1), values.get(1));
        } else if (type == QueryType.LIKE || type == QueryType.NOT_LIKE) {
          nativeQuery.setParameter(where.getParameterNames().get(0), "%" + value + "%");
        } else if (type == QueryType.LIKE_LEFT) {
          nativeQuery.setParameter(where.getParameterNames().get(0), value + "%");
        } else if (type == QueryType.LIKE_RIGHT) {
          nativeQuery.setParameter(where.getParameterNames().get(0), "%" + value);
        } else {
          nativeQuery.setParameter(where.getParameterNames().get(0), value);
        }
      }
    }
  }

  @Override
  public void update(EntityWrapper wrapper) {
    this.checkDataEmpty(wrapper.getData());
    Table table = wrapper.getDatabase().getTableInfo(wrapper.getTableName());
    Session session = wrapper.getSession();
    WhereGroupTuple whereGroupTuple = this.getWhereGroupTuple(wrapper, table);
    this.checkWhereEmpty("update", whereGroupTuple);

    List<String> columnNames = table.getColumnNames();
    List<Map<String, Object>> list =
        this.getTableValues(wrapper.getType(), table, wrapper.getData());

    for (Map<String, Object> value : list) {
      setWherePrimaryKeyColumnsValue(whereGroupTuple, value);

      List<WhereGroup> whereGroups = whereGroupTuple.getWhereGroups();

      String sql =
          StrUtil.format(
              "update {} set {} where 1=1 {}",
              table.getName(),
              columnNames.stream()
                  .map(
                      columnName ->
                          StrUtil.format(
                              "{} = :{}",
                              this.wrapperQuoteColumnName(wrapper, columnName),
                              columnName))
                  .collect(Collectors.joining(", ")),
              this.getWhereGroupClause(wrapper, whereGroups));

      NativeQuery<?> nativeQuery = session.createNativeQuery(sql);

      columnNames.forEach(
          columnName -> nativeQuery.setParameter(columnName, value.get(columnName)));

      setNativeQueryWhereParameters(nativeQuery, whereGroups);

      nativeQuery.executeUpdate();
    }
  }

  /**
   * 获取数据库表列名<br>
   * 默认支持驼峰、下划线规则，不区分大小写
   *
   * @param columnNames 数据库表列名
   * @param name 名称
   */
  private String getColumnName(List<String> columnNames, String name) {
    return columnNames.stream()
        .filter(i -> i.equalsIgnoreCase(name))
        .findFirst()
        .orElse(
            columnNames.stream()
                .filter(i -> i.equalsIgnoreCase(StrUtil.toUnderlineCase(name)))
                .findFirst()
                .orElse(null));
  }

  /**
   * 数据查询SQL for order by
   *
   * @param columnNames 数据库表列名
   * @param sorters {@link Sorter}
   */
  private String getSelectOrderByClause(List<String> columnNames, List<Sorter> sorters) {
    List<Sorter> list = new ArrayList<>();

    for (Sorter sorter : sorters) {
      List<String> names = sorter.getColumnNames(), sl = new ArrayList<>();
      for (String name : names) {
        String str = this.getColumnName(columnNames, name);
        if (StrUtil.isNotBlank(str)) {
          sl.add(str);
        }
      }
      if (!sl.isEmpty()) {
        list.add(
            new Sorter(
                sorter.getType(), sl.stream().map(Identifier::new).collect(Collectors.toList())));
      }
    }

    StringBuilder sb = new StringBuilder(list.isEmpty() ? "" : " order by ");

    for (int i = 0; i < list.size(); i++) {
      Sorter sorter = list.get(i);
      sb.append(
              StrUtil.format("{} {}", String.join(", ", sorter.getColumnNames()), sorter.getType()))
          .append(i == list.size() - 1 ? "" : ", ");
    }

    return sb.toString();
  }

  private String wrapperQuoteColumnName(EntityWrapper wrapper, String columnName) {
    try {
      Dialect dialect = wrapper.getDatabase().getDialect();
      return dialect.openQuote() + columnName + dialect.closeQuote();
    } catch (Exception e) {
      return columnName;
    }
  }

  private List<String> wrapperQuoteColumnNames(EntityWrapper wrapper, List<String> columnNames) {
    List<String> quoteColumnNames = new ArrayList<>();
    for (String columnName : columnNames) {
      quoteColumnNames.add(this.wrapperQuoteColumnName(wrapper, columnName));
    }
    return quoteColumnNames;
  }

  /**
   * 数据查询SQL
   *
   * @param ew {@link EntityWrapper}
   * @param table {@link Table}
   * @param whereGroupTuple {@link WhereGroupTuple}
   */
  private String getSelectClause(EntityWrapper ew, Table table, WhereGroupTuple whereGroupTuple) {
    List<Field> fields = this.getEntityFields(ew.getType());
    List<String> columnNames = table.getColumnNames(), queryColumnNames = ew.getQueryColumnNames();
    if (CollectionUtil.isNotEmpty(columnNames) && CollectionUtil.isEmpty(queryColumnNames)) {
      // 过滤掉不属于表结构的字段，防止报错
      List<String> names = new ArrayList<>();
      for (String queryColumnName : queryColumnNames) {
        if (columnNames.stream().anyMatch(i -> i.equalsIgnoreCase(queryColumnName))) {
          names.add(queryColumnName);
        }
      }
      queryColumnNames = names;
    }
    if (queryColumnNames.isEmpty()) {
      if (fields.isEmpty()) {
        queryColumnNames = columnNames;
      } else {
        for (String columnName : columnNames) {
          Field field = this.getEntityFieldMappingColumnName(fields, columnName);
          if (field != null) {
            JsonProperty jsonProperty = field.getAnnotation(JsonProperty.class);
            if (jsonProperty != null && jsonProperty.access() == Access.WRITE_ONLY) {
              continue;
            }
          }
          queryColumnNames.add(columnName);
        }
      }
    }

    Assert.isFalse(queryColumnNames.isEmpty(), "query [{}] column names is empty", table.getName());

    List<String> quoteColumnNames = this.wrapperQuoteColumnNames(ew, queryColumnNames);

    String schema = table.getSchema();
    if (schema == null) {
      schema = ew.getSchema();
    }

    StringBuilder sb =
        new StringBuilder(
            StrUtil.format(
                "select {} from {} where 1=1 {}",
                String.join(", ", quoteColumnNames),
                StrUtil.isNotBlank(schema)
                    ? StrUtil.format("{}.{}", schema, table.getName())
                    : table.getName(),
                this.getWhereGroupClause(ew, whereGroupTuple.getWhereGroups())));

    // TODO 暂未实现 GROUP BY

    // order by

    String selectOrderByClause = this.getSelectOrderByClause(columnNames, ew.getSorters());

    sb.append(
        StrUtil.isNotBlank(selectOrderByClause) ? StrUtil.format(" {}", selectOrderByClause) : "");

    return sb.toString();
  }

  /**
   * 构建查询结果
   *
   * @param ew {@link EntityWrapper}
   * @param list {@link Tuple}
   * @param columnConverterMap 列转换器
   */
  private List<Map<String, Object>> buildQueryResultList(
      EntityWrapper ew,
      List<Tuple> list,
      Map<String, AttributeConverter<Object, Object>> columnConverterMap) {
    return list.stream()
        .map(
            tuple -> {
              Map<String, Object> map = new HashMap<>();
              for (TupleElement<?> element : tuple.getElements()) {
                String columnName = element.getAlias();
                Object value = tuple.get(element);
                AttributeConverter<Object, Object> converter = columnConverterMap.get(columnName);
                if (converter != null) {
                  value = converter.convertToEntityAttribute(value);
                }
                map.put(ew.getQueryColumnNameAlias(columnName), value);
              }

              return map;
            })
        .collect(Collectors.toList());
  }

  /**
   * 获取查询条件
   *
   * @param ew {@link EntityWrapper}
   * @param table {@link Table}
   */
  private WhereGroupTuple getSelectWhereGroupTuple(EntityWrapper ew, Table table) {
    WhereGroupTuple whereGroupTuple = this.getWhereGroupTuple(ew, table);
    List<Map<String, Object>> list = this.getTableValues(ew.getType(), table, ew.getData());
    if (whereGroupTuple.getType() == WhereGroupType.PRIMARY_KEY) {
      if (CollectionUtil.isNotEmpty(list)) {
        this.setWherePrimaryKeyColumnsValue(whereGroupTuple, list.get(0));
      } else {
        whereGroupTuple.getWhereGroups().clear();
      }
    }

    return whereGroupTuple;
  }

  private class NativeQueryTuple {
    @Getter private final String sql;
    @Getter private final WhereGroupTuple whereGroupTuple;
    @Getter private final Session session;
    @Getter private final NativeQuery<Tuple> nativeQuery;
    @Getter private final Map<String, AttributeConverter<Object, Object>> columnConverterMap;

    public NativeQueryTuple(
        String sql,
        WhereGroupTuple whereGroupTuple,
        Session session,
        NativeQuery<Tuple> nativeQuery,
        Map<String, AttributeConverter<Object, Object>> columnConverterMap) {
      this.sql = sql;
      this.whereGroupTuple = whereGroupTuple;
      this.session = session;
      this.nativeQuery = nativeQuery;
      this.columnConverterMap = columnConverterMap;
    }
  }

  private NativeQueryTuple getNativeQueryTuple(EntityWrapper wrapper) {
    Database database = wrapper.getDatabase();
    String schema = wrapper.getSchema();
    // 若存在schema，则证明指定了其他的具有权限的数据库的表或视图
    Table table =
        StrUtil.isNotBlank(schema)
            ? database.getTableInfo(
                null, schema, wrapper.getTableName(), TableType.TABLE, TableType.VIEW)
            : database.getTableInfo(wrapper.getTableName());
    Session session = wrapper.getSession();
    // 查询条件
    WhereGroupTuple whereGroupTuple = this.getSelectWhereGroupTuple(wrapper, table);
    // 查询语句
    String sql = this.getSelectClause(wrapper, table, whereGroupTuple);
    // 转换函数
    Map<String, AttributeConverter<Object, Object>> columnConverterMap =
        this.getColumnConverterMap(wrapper.getType(), table.getColumnNames());

    NativeQuery<Tuple> nativeQuery = session.createNativeQuery(sql, Tuple.class);
    this.setNativeQueryWhereParameters(nativeQuery, whereGroupTuple.getWhereGroups());

    return new NativeQueryTuple(sql, whereGroupTuple, session, nativeQuery, columnConverterMap);
  }

  @Override
  public List<Map<String, Object>> find(EntityWrapper wrapper) {
    NativeQueryTuple tuple = this.getNativeQueryTuple(wrapper);

    // 如果是像oracle那种数据库中clob类型的字段，查询到了之后应该是流，所以要是在此处关闭session
    // 就会出现 java.lang.IllegalStateException: Session/EntityManager is closed 错误
    // 提前关闭了session，所以要使用aop来处理这种情况;或者在查询得到元组Tuple的时候就进行处理也行
    // session.close();
    // 在Aspect中拦截关闭

    return this.buildQueryResultList(
        wrapper, tuple.getNativeQuery().getResultList(), tuple.getColumnConverterMap());
  }

  /**
   * 分页查询总页数获取
   *
   * @param sql 数据查询SQL
   * @param whereGroupTuple {@link WhereGroupTuple}
   */
  private Long queryPageableTotal(String sql, Session session, WhereGroupTuple whereGroupTuple) {
    String alias = "total";
    NativeQuery<Tuple> nativeQuery =
        session.createNativeQuery(
            StrUtil.format("select count(1) as {} from ( {} ) t", alias, sql), Tuple.class);
    this.setNativeQueryWhereParameters(nativeQuery, whereGroupTuple.getWhereGroups());

    List<Tuple> data = nativeQuery.getResultList();

    Object value = data.get(0).get(alias);

    if (value instanceof BigDecimal) {
      return ((BigDecimal) value).longValue();
    } else if (value instanceof Long || value instanceof Integer) {
      return Long.valueOf(value.toString());
    } else if (value instanceof BigInteger) {
      return ((BigInteger) value).longValue();
    } else {
      throw new RuntimeException(
          "Convert -> \noriginal value Type: "
              + value.getClass().getName()
              + ", convert value: "
              + value);
    }
  }

  @Override
  public Pageable<Map<String, Object>> pageable(EntityWrapper wrapper, int pageNo, int pageSize) {
    NativeQueryTuple tuple = this.getNativeQueryTuple(wrapper);

    NativeQuery<Tuple> nativeQuery = tuple.getNativeQuery();

    nativeQuery.setFirstResult((pageNo - 1) * pageSize);
    nativeQuery.setMaxResults(pageSize);

    List<Map<String, Object>> data =
        this.buildQueryResultList(
            wrapper, nativeQuery.getResultList(), tuple.getColumnConverterMap());
    Long total =
        this.queryPageableTotal(tuple.getSql(), tuple.getSession(), tuple.getWhereGroupTuple());

    Pageable<Map<String, Object>> pageable = new Pageable<>();
    pageable.setPageNo(pageNo);
    pageable.setPageSize(pageSize);
    pageable.setData(data);
    pageable.setTotal(total);

    return pageable;
  }
}
